package com.feib.ebilling.job.service;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.Statement;
import java.text.DateFormat;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.HashMap;
import java.util.Locale;
import java.util.Map;

import javax.annotation.Resource;

import org.springframework.jdbc.datasource.DataSourceUtils;
import org.springframework.jdbc.support.JdbcUtils;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;

import com.feib.ebilling.job.dao.ThirdPartyFeedbackDao;
import com.feib.ebilling.job.util.StringUtils;


/**
 * @title (#)FeeLiquidationFileService.java<br>
 * @description 中租車貸策略聯盟手續費清算明細檔產生<br>
 * @author Fiona Huang<br>
 * @version 1.0.0 2011/11/09
 * @copyright Far Eastern International Bank Copyright (c) 2011<br>
 * @2011/11/09 create by Fiona Huang<br>
 */

@Service("feeLiquidationFileService")
public class FeeLiquidationFileService extends BaseService  {

	public static Map<String, String> CHANEL_CODE = new HashMap<String, String>();	
	public static Map<String, String> CHANEL_FEE = new HashMap<String, String>();
	public static Integer[] POST_FEE = new Integer[4];
    static
    {
        CHANEL_CODE.put("7111111", "1");        
        CHANEL_CODE.put("TFM", "2");
        CHANEL_CODE.put("HILIFE", "3");
        CHANEL_CODE.put("OKCVS", "4");
        CHANEL_CODE.put("POST", "5");
    }
    
    @Resource(name="thirdPartyFeedbackDao")
    private ThirdPartyFeedbackDao thirdPartyFeedbackDao;
    
    @Transactional(readOnly = false)
    public String writeoffByFile(String processDate, String group_no) {
    	StringBuffer sbf = new StringBuffer(); 
        // 取得 DB Connection 
        Connection con = DataSourceUtils.getConnection(thirdPartyFeedbackDao.getDataSource());
        Statement stmt = null;
        try {
            stmt = con.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
            StringBuffer sb = new StringBuffer();
            sb.append(" SELECT COMMISSION_CODE, A.CHAINSTORE_AMOUNT, PAYER_AMOUNT, GROUP_AMOUNT");
    		sb.append("   FROM APP_GROUP A, FORMAT_DEFINITION B, FORMAT_DEF_COMMISSION C, COMMISSION_CODE_TABLE D");
    		sb.append("   WHERE GROUP_NO='" + group_no + "' AND A.GROUP_PK = B.GROUP_PK	AND B.FORMAT_DEF_PK=C.FORMAT_DEF_PK");
    		sb.append("   AND C.COMMISSION_CODE_PK=D.COMMISSION_CODE_PK AND COMMISSION_CODE <> 'XXX'");
    		ResultSet rs = null;
    		rs = stmt.executeQuery(sb.toString());
    		/* 超商手續費  ( 超商手續費 + 銀行帳務處理費 ) */
    		while (rs.next()){
    			CHANEL_FEE.put(rs.getString("COMMISSION_CODE"),
    					                    String.valueOf(rs.getInt("CHAINSTORE_AMOUNT") + rs.getInt("GROUP_AMOUNT")));
    			logger.info("超商代碼:{}, 手續費:{}", rs.getString("COMMISSION_CODE"), String.valueOf(rs.getInt("CHAINSTORE_AMOUNT") + rs.getInt("GROUP_AMOUNT")));
    		}
    		
    		sb.delete(0, sb.length());
    		sb.append(" SELECT POST_AMOUNT, POST_LEVEL1, POST_LEVEL2, POST_LEVEL3");
     		sb.append("   FROM APP_GROUP");
     		sb.append("  WHERE GROUP_NO='" + group_no + "'");
     		rs = null;
     		rs = stmt.executeQuery(sb.toString());
     		/* 郵局手續費  ( 級距手續費 + 銀行帳務處理費 ) */
     		if  (rs.next()){ 
     			POST_FEE[0] = rs.getInt("POST_AMOUNT");
     			POST_FEE[1] = rs.getInt("POST_LEVEL1");
     			POST_FEE[2] = rs.getInt("POST_LEVEL2");
     			POST_FEE[3] = rs.getInt("POST_LEVEL3");
     			logger.info("郵局通路, 帳務處理費:{}", POST_FEE[0]);
     			logger.info("郵局通路, 級距1:{}", POST_FEE[1]);
     			logger.info("郵局通路, 級距2:{}", POST_FEE[2]);
     			logger.info("郵局通路, 級距3:{}", POST_FEE[3]);
     		}
    		
            sb.delete(0, sb.length());
            sb.append(" SELECT A.UID, A.FBFILENAME, A.FBTIME, A.ADAY,");
    		sb.append("        A.ATIME, A.ERR_MSG, A.CHANNEL, A.TXAMT, A.USERNO, A.BARCDOE1,");
    		sb.append("        B.*, ");
    		sb.append("        P.PAYMENT_PK, P.GROUP_PK, P.WRITE_OFF_PAYMENT_PK, P.PAYMENT_NAME,");
    		sb.append("        P.VIRTUAL_ACCOUNT, P.PERIOD, P.PAYER_ID, P.PAYER_NAME, P.STUDENT_ID,");
    		sb.append("        P.PAY_DUE, P.TOTAL_AMOUNT, P.TOTAL_LOANABLE_AMOUNT, P.BARCODE_STORES_2,");
    		sb.append("        C.CANCEL_TYPE,");
    		sb.append("        E.ATTRIBUTE1");
    		sb.append("   FROM FEEDBACK_LOG A");
    		sb.append("   INNER JOIN (SELECT G.GROUP_PK , G.GROUP_NO, G.GROUP_NAME,");
    		sb.append("                     R.VIRTUAL_ACCOUNT_NO ,LEN(R.VIRTUAL_ACCOUNT_NO) AS length");
    		sb.append("                FROM REAL_ACCOUNT R ");
    		sb.append("                     JOIN APP_GROUP G ON R.GROUP_PK = G.GROUP_PK ");
    		sb.append("               WHERE G.GROUP_TYPE ='4' and G.GROUP_NO='" + group_no + "') B ");
    		sb.append("   ON ");
    		sb.append("   LEFT(RTRIM(A.USERNO), B.length) = B.VIRTUAL_ACCOUNT_NO");
    		sb.append("   LEFT JOIN PAYMENT P");
    		sb.append("   ON ");
    		sb.append("   RTRIM(A.USERNO) = P.VIRTUAL_ACCOUNT " );
    		sb.append("   LEFT JOIN EXTRA_COLUMNS E");
    		sb.append("   ON ");
    		sb.append("   P.EXTRA_COLUMNS_PK = E.EXTRA_COLUMNS_PK " );
    		sb.append("   LEFT JOIN CARLOAN_CANCEL C on SUBSTRING(A.USERNO,1,14) = SUBSTRING(C.VIRTUAL_ACCOUNT,1,14)");
    		sb.append("  WHERE A.FEEDBACK_TYPE = 2 AND ");
    		sb.append("        CONVERT(CHAR(8), A.FBTIME, 112) Like '" + processDate + "%'");
    		sb.append("  ORDER BY A.FBTIME, A.UID");
    		//logger.info("SQL:{}", sb.toString());
    		rs = null;
            try {
                rs = stmt.executeQuery(sb.toString());                
                SimpleDateFormat dateFormat = new SimpleDateFormat("yyyyMMdd");                
                long total = 0;
                long total_amount = 0;
                long total_fee = 0;
                while (rs.next()){
                	 /* 轉檔日期 */
                	 sbf.append(StringUtils.PadString("L", " ", 8, dateFormat.format((Date)rs.getDate("FBTIME"))));
                	 /* 銀行帳號 */
                	 sbf.append(StringUtils.PadString("L", " ", 16, rs.getString("USERNO")));
                	 /* 合約編號 */
                	 sbf.append(StringUtils.PadString("L", " ", 14, rs.getString("PAYER_ID")));
                	 /* 已結清或代償之案件放空值(右靠左補零) */
                	 if(rs.getString("CANCEL_TYPE") == null){
                		/* 期數 */
                		sbf.append(StringUtils.PadString("R", "0", 3, rs.getString("USERNO").substring(14,16)));
                		/* 應繳日期 */
                		DateFormat formatter = new SimpleDateFormat("EEE MMM dd hh:mm:ss z yyyy" , Locale.ENGLISH);
                		Date paydate = (Date)formatter.parse(rs.getString("ATTRIBUTE1"));
                		sbf.append(StringUtils.PadString("L", "0", 8, dateFormat.format(paydate)));
                     }else{
                    	/* 期數、應繳日期空白 */
                		sbf.append(StringUtils.PadString("R", " ", 3, ""));
                		sbf.append(StringUtils.PadString("R", " ", 8, ""));
                		logger.info("車商已買回或提前清償:{}", rs.getString("USERNO"));
                	 }
                	 /* 實際繳款日 */
                	 sbf.append(StringUtils.PadString("L", " ", 8, rs.getString("ADAY")));
                	 /* 繳費金額 */
                	 sbf.append(StringUtils.PadString("R", "0", 9, rs.getString("TXAMT").trim()));
                	 total_amount +=  rs.getInt("TXAMT");
                	 /* 通路別 */
                	 sbf.append(StringUtils.PadString("L", " ", 1, CHANEL_CODE.get(rs.getString("CHANNEL"))));
                	 /* 手續費 */
                	 if (!rs.getString("CHANNEL").equals("POST")) {
                		 /* 四大超商 */
	                	 String barcode = rs.getString("BARCDOE1");
	                	 barcode = barcode.substring(barcode.length() - 3, barcode.length());
	                	 sbf.append(StringUtils.PadString("R", "0", 2, CHANEL_FEE.get(barcode)));
	                	 total_fee += Integer.parseInt(CHANEL_FEE.get(barcode));
                	 }else{
                		 /* 郵局 */
                		 if (rs.getInt("TXAMT") > 0 && rs.getInt("TXAMT") <= 100){
                			 sbf.append(StringUtils.PadString("R", "0", 2, String.valueOf(POST_FEE[0] + POST_FEE[1])));
                			 total_fee += POST_FEE[0] + POST_FEE[1];
                		 }else if (rs.getInt("TXAMT") > 100 && rs.getInt("TXAMT") <= 1000){
                			 sbf.append(StringUtils.PadString("R", "0", 2, String.valueOf(POST_FEE[0] + POST_FEE[2])));
                			 total_fee += POST_FEE[0] + POST_FEE[2];
                		 }else if (rs.getInt("TXAMT") > 1000 ){                			 
                			 sbf.append(StringUtils.PadString("R", "0", 2, String.valueOf(POST_FEE[0] + POST_FEE[3])));
                			 total_fee += POST_FEE[0] + POST_FEE[2];
                		 }else{
                			 logger.error("郵局繳款金額錯誤，無法計算手續費:{}", rs.getInt("TXAMT"));
                		 }
                	 }
                	 sbf.append("\r\n");                	 
                	 total ++;
                } // end of while (rs.next())
                if (processDate.length() == 6){
                	/* 月檔需要加總於最後一筆 */
                	sbf.append(StringUtils.PadString("R", "0", 12, String.valueOf(total)));
                	sbf.append(StringUtils.PadString("R", "0", 12, String.valueOf(total_amount)));
                	sbf.append(StringUtils.PadString("R", "0", 12, String.valueOf(total_fee) )); 
                	sbf.append("\r\n");  
                }
                logger.info("中租車貸策略聯盟手續費清算明細檔筆數:{}", total);
            }
            finally {
                JdbcUtils.closeResultSet(rs);
            }
        }
        catch (Exception e) {
            JdbcUtils.closeStatement(stmt);
            stmt = null;
            DataSourceUtils.releaseConnection(con, thirdPartyFeedbackDao.getDataSource());
            con = null;
            throw new RuntimeException(e);
        }
        finally
        {
            JdbcUtils.closeStatement(stmt);
            stmt = null;
            DataSourceUtils.releaseConnection(con, thirdPartyFeedbackDao.getDataSource());
            con = null;
            
        }
        return sbf.toString();   
    }
}
